// 引入mysql
var mysql = require('mysql')
// 引入mysql连接配置
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'testdata'
});
var userData = {
  // 查询所有数据
  queryAll: function (req, res, next) {
    let queryAll = 'SELECT * FROM userdata'
    connection.query(queryAll, function (err, result) {
      if (result != '') {
        res.json({
          code: '200',
          msg: '全部查找成功',
          data: result
        })
      } else {
        res.json({
          code: '1',
          msg: '操作失败'
        })
      }
    })
  },
  // 按照条件查询
  queryBySome: function (req, res, next) {
    let queryBySome = 'SELECT * FROM userdata WHERE 1=1'
    var id = req.query.id;
    var userName = req.query.userName;
    var userMsg = req.query.userMsg;
    var userConcat = req.query.userConcat;
    if (id != null && id != "") {
      queryBySome += " and id=" + id
    }
    if (userName != null && userName != "") {
      queryBySome += " and userName=" + userName
    }
    if (userMsg != null && userMsg != "") {
      queryBySome += " and userMsg=" + userMsg
    }
    if (userConcat != null && userConcat != "") {
      queryBySome += " and userConcat=" + userConcat
    }
    connection.query(queryBySome, function (err, result) {
      if (result != '') {
        res.json({
          code: '200',
          msg: '按条件查找成功',
          data: result
        })
      } else {
        res.json({
          code: '1',
          msg: '操作失败'
        })
      }
    });

  },
  // 添加一条数据
  addSome: function (req, res, next) {
    let addSome = 'INSERT INTO userdata(id, userName, userMsg, userConcat) VALUES('
    var id = req.query.id;
    var userName = req.query.userName;
    var userMsg = req.query.userMsg;
    var userConcat = req.query.userConcat;
    if (id != null && id != "") {
      addSome += id + ","
    } else {
      res.json({
        code: '1',
        msg: '操作失败'
      })
    }
    if (userName != null && userName != "") {
      addSome += userName + ","
    } else {
      res.json({
        code: '1',
        msg: '操作失败'
      })
    }
    if (userMsg != null && userMsg != "") {
      addSome += userMsg + ","
    } else {
      res.json({
        code: '1',
        msg: '操作失败'
      })
    }
    if (userConcat != null && userConcat != "") {
      addSome += userConcat + ")"
    } else {
      res.json({
        code: '1',
        msg: '操作失败'
      })
    }
    connection.query(addSome, function (err, result) {
      if (result != '') {
        res.json({
          code: '200',
          msg: '添加成功'
        })
      } else {
        res.json({
          code: '1',
          msg: '操作失败'
        })
      }
    })
  },
  // 删除一条数据
  deleteSome: function (req, res, next) {
    var id = req.query.id;
    let deleteSome = 'DELETE FROM userdata WHERE id='
    if (id != null && id != "") {
      deleteSome += id
    }
    connection.query(deleteSome, function (err, result) {
      if (result != '') {
        res.json({
          code: '200',
          msg: '删除成功',
        })
      } else {
        res.json({
          code: '1',
          msg: '操作失败'
        })
      }
    })
  },
  // 编辑一条数据
  updateSome: function (req, res, next) {
    let updateSome = 'UPDATE userdata SET'
    var id = req.query.id;
    var userName = req.query.userName;
    var userMsg = req.query.userMsg;
    var userConcat = req.query.userConcat;
    updateSome += " userName='" + userName + "',"
    updateSome += " userMsg='" + userMsg + "',"
    updateSome += " userConcat='" + userConcat + "'"
    updateSome += " WHERE id=" + id
    console.log('updateSome', updateSome)
    connection.query(updateSome, function (err, result) {
      if (result != '') {
        res.json({
          code: '200',
          msg: '修改成功',
        })
      } else {
        res.json({
          code: '1',
          msg: '操作失败'
        })
      }
    })
  },
}
module.exports = userData